
importdata_header() {
cat <<-EOF
<%@Language=Vbscript CodePage=65001%>
<%
Option Explicit
Response.Buffer=true
Response.Expires=-1
Session.codepage=65001
%>
<!--#include virtual='/GDCRM/library/sys_SystemCheck_S.asp' -->
<%
EOF
}

importdata_related_data() {
cat <<-EOF
'*******************************************************************
'定義程式參數
'*******************************************************************
dim strKey
Dim $(strKeyValue_list $KEYMULTY_CNT)
strKey = trim(Request("gd_Key"))
if instr(1,strKey,",") > 0 then
$(strKeyValue_assign)
Else
    Response.Write "Need Key Value"
    Response.End
End If
'other related data define.
'...
EOF
}


importdata_get_attach_info() {
cat <<-EOF
'*******************************************************************
'取得當比資料的附件相關資料
'*******************************************************************
Dim strSQLTemp
Dim rstTemp
Dim strExcelSource

strSQLTemp = "Select * from fn_Data_Attachments() " & _
	     "where AttachID=(Select AttachID from ${TEMPLATE} " & _
			      " where " & $(modifydata_where_filter) & ")"

' DeptID=N'" &  strKeyValue1 & "'  AND " & _ 
'				    "PeriodId=N'" & strKeyValue2 & "' AND " & _
'				    "WhrsId=N'" & strKeyValue3 & "')"

'Response.Write strSQLTemp
'Response.End

Set rstTemp = objPublic.CreateRecordset(strSQLTemp, Application("a_CRMConnect"))

If rstTemp.RecordCount = 1 Then
	strExcelSource = Application("a_DocRootPath") & _
				    "${TEMPLATE}\" &  _
				    Left(rstTemp.Fields("FileID").value,2) & "\" & _
				    rstTemp.Fields("FileID").value & "\" & _
				    rstTemp.Fields("FileName").value
else
    if rstTemp.RecordCount < 1 then
        Response.Write "Attachment data not found, Please check attachment data"
    End if
    if rstTemp.RecordCount > 1 then
        Response.Write "Attachment data more than one file, Please check attachment data"
    End if
    'Response.Write "Attachment data error, Please check attachment data"
    Response.End
End if
EOF
}

importdata_prepare() {
cat <<-EOF
'importdata_prepare()
Dim cnnExcel
Dim strExcelConn
Dim rstExcel
Dim strSQL

Dim strSQL$(__related_table)
Dim rst$(__related_table)

strSQL$(__related_table) = "Select * from $(__related_table) where (1=2)"
Set rst$(__related_table) = objPublic.CreateRecordset(strSQL$(__related_table), Application("a_CRMConnect"))

 
strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0" & _
                   ";Data Source=" & strExcelSource & _
                   ";Extended Properties=""Excel 8.0;HDR=Yes"""

Set cnnExcel = Server.CreateObject("ADODB.Connection")
Set rstExcel = Server.CreateObject("ADODB.Recordset")

'工作表名稱需要有固定名稱:[$(__wksheet)]
'ATTACH_FIELDS: $(__attach_fields)
'strSQL = "Select ${KEY_MULTY}, $(__attach_fields) from [$(__wksheet)$]"
strSQL = "Select $(__select_fields)  from [$(__wksheet)$]"

cnnExcel.CursorLocation = 3
cnnExcel.Open strExcelConn

rstExcel.CursorLocation = 3
rstExcel.CursorType = 1
rstExcel.LockType = 3

EOF
}

importdata_attach_open() {
cat <<-EOF
rstExcel.Open strSQL,cnnExcel
EOF
}

importdata_attach_check() {
#'Check attachment data.
cat <<-EOF
'importdata_attach_check()
Dim intCount
rstExcel.MoveFirst
EOF

if [ "$(__is_first_title)". = "TRUE". ] ; then echo rstExcel.MoveNext; fi
if [ "$IS_CHECK_KEY_MULTY". = "TRUE". ] ; then
cat <<-EOF
For intCount = 1 To (rstExcel.RecordCount-1)

    if ( $(_importdata_check_key) ) then
        response.Write "Record #: " & cstr( intCount + 2 ) & " error, "   & _
			$(_importdata_msg_string1)
        response.End
    end if	    
    rstExcel.MoveNext
Next
EOF
fi
}

importdata_write() {
cat <<-EOF
'importdata_write()
rstExcel.MoveFirst
EOF

#if [ "$IS_FIRST_TITLE". = "TRUE". ] ; then echo rstExcel.MoveNext; fi
if [ "$(__is_first_title)". = "TRUE". ] ; then echo rstExcel.MoveNext; fi
cat <<-EOF
For intCount = 1 To (rstExcel.RecordCount-1)

    rst$(__related_table).AddNew
    ' generated by:  _importdata_key_assign()
    $(_importdata_key_assign)
    
    ' generated by:  _importdata_attach_fields()
    $( _importdata_attach_fields)

    ' generated by:  _importdata_not_attach_fields(), these fields define in related table but not in attach file.
    $( _importdata_not_attach_fields)

    rstExcel.MoveNext
Next

objPublic.UpdateRecordset rst$(__related_table), Application("a_CRMConnect")
EOF
}

importdata_tailer() {
cat <<-EOF
'importdata_tailer()
If Err.number = 0 then
    Response.Write "OK"
Else
    Response.Write Err.Description
End if
%>
EOF
}

_importdata_attach_fields() {

    #local wc=`echo $ATTACH_FIELDS | sed 's/,/ /g' | wc -w`
    local wc=`echo $(__attach_fields) | sed 's/,/ /g' | wc -w`
    for i in `echo $(__attach_fields) | sed 's/,/ /g'` ; do
	#echo -e "\\trst${RELATED_TABLE}(\"${i}\").value=rstExcel(\"${i}\").value"
        if [ $i != `echo $KEY_MULTY | sed "s/^.*$i.*$/$i/"` ] ; then
	    echo -e "\\trst$(__related_table)(\"${i}\").value=rstExcel(\"${i}\").value"
	else
	    echo -e "'" "\\trst$(__related_table)(\"${i}\").value=rstExcel(\"${i}\").value"

	fi
    done

}

_importdata_not_attach_fields() {
    if [ "$NOT_ATTACH_FIELDS". = "". ] ; then
	echo "'"'No $NOT_ATTACH_FIELDS defined'
    else
	for na_att in `echo $NOT_ATTACH_FIELDS` ; do
	    fd="`echo $na_att | sed 's/#.*$//g'`"
	    value="`echo $na_att | sed 's/^.*#//g'`"
	    echo -e "\\trst$(__related_table)(\"${fd}\").value=$value"
	done

    fi
}    
_importdata_key_assign() {
    for i in `seq $KEYMULTY_CNT` ; do
	if [ "$i". == "$KEYMULTY_CNT". ] ; then
	    tailer=''
	else
	    tailer=' & ", " & _ '
	fi
        fd=`echo $KEY_MULTY | gawk -F ',' "{print $(echo '$'$(echo $i))}"`
	#echo  -e "\\trst""${RELATED_TABLE}(\""$fd"\").value=strKeyValue"$i
	echo  -e "\\trst""$(__related_table)(\""$fd"\").value=strKeyValue"$i
    done
}

_importdata_msg_string1() {
    if [ "$ATTACH_CHKKEY". = "".  ] ; then
	for i in `seq $KEYMULTY_CNT` ; do
	    if [ "$i". == "$KEYMULTY_CNT". ] ; then
		tailer=''
	    else
		tailer=' & ", " & _ '
	    fi
	    fd=`echo $KEY_MULTY | gawk -F ',' "{print $(echo '$'$(echo $i))}"`
	    echo  -e "\\t\"$fd: \" & "strKeyValue$i' & "(" & cstr(rstExcel("'$fd'").value) & ")"' \
		    $tailer
	done
    else
	lastkey=`echo $ATTACH_CHKKEY |sed 's/^.*,//g'`
	tailer=' & ", " & _ '
	for key in $(echo $ATTACH_CHKKEY | tr ',' ' ') ; do
	    
	    if [ "$key". = "$lastkey". ] ; then tailer=''; fi

	    num="$(echo $KEY_MULTY | tr ',' '\n' | grep -n $key)"
	    if [ "$num". = "". ] ; then
		echo "$key not key name, please check"
	    else
		n=`echo $num | sed 's/:.*$//g'`
	         echo  -e "\\t\"$key: \" & "strKeyValue$n' & "(" & cstr(rstExcel("'$key'").value) & ")"' \
		    $tailer
	    fi
       done
    fi
}

_importdata_check_key() {

    if [ "$ATTACH_CHKKEY". = "".  ] ; then
	for i in `seq $KEYMULTY_CNT` ; do
	    if [ "$i". == "$KEYMULTY_CNT". ] ; then
		tailer=''
	    else
		tailer=' or '
	    fi
	    echo  -n "cstr(rstExcel(\""`echo $KEY_MULTY | 
			gawk -F ',' "{print $(echo '$'$(echo $i))}"`"\").value) <> "strKeyValue$i"$tailer"
	done
    else

	lastkey=`echo $ATTACH_CHKKEY |sed 's/^.*,//g'`
	tailer=' or '
	for key in $(echo $ATTACH_CHKKEY | tr ',' ' ') ; do
	    
	    if [ "$key". = "$lastkey". ] ; then tailer=''; fi

	    num="$(echo $KEY_MULTY | tr ',' '\n' | grep -n $key)"

	    if [ "$num". = "". ] ; then
		echo "$key not key name, please check"
	    else
		n=`echo $num | sed 's/:.*$//g'`
		echo  -n "cstr(rstExcel(\""$key"\").value) <> "strKeyValue$n"$tailer"
	    fi
       done
    fi
}

ws_template_importdata() {
    KEYMULTY_CNT=`echo $KEY_MULTY | sed 's/,/ /g' | wc -w`
    #echo 1>&2 $KEYMULTY_CNT
    importdata_header
    importdata_related_data
    importdata_get_attach_info
    importdata_prepare
    importdata_attach_open

    importdata_attach_check
    importdata_write
    
    importdata_tailer
}


